Telegram Group & Telegram Channel
🖥 Задача: Анализ пользовательского поведения с аномалиями в SQL

## Условие задачи:

Дана таблица user_events со следующей структурой:


CREATE TABLE user_events (
user_id INT,
event_time TIMESTAMP,
event_type VARCHAR(50),
platform VARCHAR(50)
);


🎯 Каждая строка описывает событие пользователя:
- user_id — идентификатор пользователя,
- event_time — время события,
- event_type — тип события (`login`, purchase, logout, error и т.д.),
- platform — платформа (`iOS`, Android, `Web`).

Требуется:

1. Найти пользователей, которые:
- Выполнили покупку (`purchase`),
- Но не заходили в систему (`login`) в течение последних 7 дней перед покупкой.

2. Найти пользователей, у которых:
- Более 30% всех событий за последний месяц составляют события типа error.

3. Рассчитать для каждого пользователя:
- Среднее время между входом (`login`) и следующим выходом (`logout`).
- Если logout отсутствует после login — игнорировать такую сессию.

---

## Дополнительные условия:

- Считайте, что данные могут быть объемными: миллионы строк.
- Решение должно быть оптимизировано: избегайте подзапросов в подзапросах без индексов, старайтесь минимизировать количество проходов по данным.
- Можно использовать оконные функции (`WINDOW FUNCTIONS`) и временные таблицы (`CTE`) для упрощения запросов.
- Платформу можно игнорировать в расчетах.

---

## Что оценивается:

- Умение использовать оконные функции и агрегаты.
- Умение правильно интерпретировать условия задачи в SQL-операции.
- Оптимизация запросов под большие объемы данных.
- Чистота, читаемость и структурированность кода SQL-запросов.

---

Примечание:
Эта задача проверяет как технические навыки работы с SQL, так и внимательность к деталям формулировки задачи. Небрежная реализация может дать неверные результаты, особенно на больших данных.

🔥 Подсказки и намёки для решения задачи


## Задание 1: Найти пользователей с покупками без логина за последние 7 дней

**Намёк:**
- Используйте оконную функцию LAG() или MAX() с фильтрацией событий login.
- Для каждой покупки проверяйте, был ли login в пределах 7 дней до события purchase.
- Можно применить LEFT JOIN событий login к событиям purchase.

## Задание 2: Найти пользователей с долей ошибок > 30%

**Намёк:**
- Используйте оконные функции COUNT(*) и SUM(CASE WHEN event_type = 'error' THEN 1 ELSE 0 END).
- Постройте долю ошибок на основе всех событий пользователя за последние 30 дней (`WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'`).

## Задание 3: Рассчитать среднее время между login и следующим logout

**Намёк:**
- Используйте оконную функцию LEAD() для поиска следующего события после login.
- Пара login -> logout должна иметь корректный порядок по времени.
- Отбрасывайте случаи, где следующего logout нет или это событие другого типа.

@sqlhub
Please open Telegram to view this post
VIEW IN TELEGRAM



tg-me.com/sqlhub/1857
Create:
Last Update:

🖥 Задача: Анализ пользовательского поведения с аномалиями в SQL

## Условие задачи:

Дана таблица user_events со следующей структурой:


CREATE TABLE user_events (
user_id INT,
event_time TIMESTAMP,
event_type VARCHAR(50),
platform VARCHAR(50)
);


🎯 Каждая строка описывает событие пользователя:
- user_id — идентификатор пользователя,
- event_time — время события,
- event_type — тип события (`login`, purchase, logout, error и т.д.),
- platform — платформа (`iOS`, Android, `Web`).

Требуется:

1. Найти пользователей, которые:
- Выполнили покупку (`purchase`),
- Но не заходили в систему (`login`) в течение последних 7 дней перед покупкой.

2. Найти пользователей, у которых:
- Более 30% всех событий за последний месяц составляют события типа error.

3. Рассчитать для каждого пользователя:
- Среднее время между входом (`login`) и следующим выходом (`logout`).
- Если logout отсутствует после login — игнорировать такую сессию.

---

## Дополнительные условия:

- Считайте, что данные могут быть объемными: миллионы строк.
- Решение должно быть оптимизировано: избегайте подзапросов в подзапросах без индексов, старайтесь минимизировать количество проходов по данным.
- Можно использовать оконные функции (`WINDOW FUNCTIONS`) и временные таблицы (`CTE`) для упрощения запросов.
- Платформу можно игнорировать в расчетах.

---

## Что оценивается:

- Умение использовать оконные функции и агрегаты.
- Умение правильно интерпретировать условия задачи в SQL-операции.
- Оптимизация запросов под большие объемы данных.
- Чистота, читаемость и структурированность кода SQL-запросов.

---

Примечание:
Эта задача проверяет как технические навыки работы с SQL, так и внимательность к деталям формулировки задачи. Небрежная реализация может дать неверные результаты, особенно на больших данных.

🔥 Подсказки и намёки для решения задачи


## Задание 1: Найти пользователей с покупками без логина за последние 7 дней

**Намёк:**
- Используйте оконную функцию LAG() или MAX() с фильтрацией событий login.
- Для каждой покупки проверяйте, был ли login в пределах 7 дней до события purchase.
- Можно применить LEFT JOIN событий login к событиям purchase.

## Задание 2: Найти пользователей с долей ошибок > 30%

**Намёк:**
- Используйте оконные функции COUNT(*) и SUM(CASE WHEN event_type = 'error' THEN 1 ELSE 0 END).
- Постройте долю ошибок на основе всех событий пользователя за последние 30 дней (`WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'`).

## Задание 3: Рассчитать среднее время между login и следующим logout

**Намёк:**
- Используйте оконную функцию LEAD() для поиска следующего события после login.
- Пара login -> logout должна иметь корректный порядок по времени.
- Отбрасывайте случаи, где следующего logout нет или это событие другого типа.

@sqlhub

BY Data Science. SQL hub


Warning: Undefined variable $i in /var/www/tg-me/post.php on line 283

Share with your friend now:
tg-me.com/sqlhub/1857

View MORE
Open in Telegram


Data Science SQL hub Telegram | DID YOU KNOW?

Date: |

Why Telegram?

Telegram has no known backdoors and, even though it is come in for criticism for using proprietary encryption methods instead of open-source ones, those have yet to be compromised. While no messaging app can guarantee a 100% impermeable defense against determined attackers, Telegram is vulnerabilities are few and either theoretical or based on spoof files fooling users into actively enabling an attack.

Newly uncovered hack campaign in Telegram

The campaign, which security firm Check Point has named Rampant Kitten, comprises two main components, one for Windows and the other for Android. Rampant Kitten’s objective is to steal Telegram messages, passwords, and two-factor authentication codes sent by SMS and then also take screenshots and record sounds within earshot of an infected phone, the researchers said in a post published on Friday.

Data Science SQL hub from pl


Telegram Data Science. SQL hub
FROM USA